//
//  DataHelper.m
//  WeddingApp
//
//  Created by toannt on 12/5/12.
//
//

#import "DataHelper.h"
#import "FMDatabase.h"
#import "NSDate+Ex.h"


#define kDatabaseName @"CalCounterDB"

@interface DataHelper()
@property (strong, nonatomic) FMDatabase *db;
@end

@implementation DataHelper

static DataHelper *sharedDb;

+ (DataHelper *)sharedDatabase{
    if (sharedDb == nil) {
        sharedDb = [[DataHelper alloc] initWithPath:[DataHelper databasePath]];
    }
    return sharedDb;
}

- (id)initWithPath:(NSString *)path{
    NSAssert(path, @"path must not be nil....");
    self = [super init];
    if (self) {
        self.db = [FMDatabase databaseWithPath:path];
        self.db.logsErrors = YES;
    }
    return self;
}

#pragma mark - Private methods
- (BOOL)insertTable:(NSString *)tableName withParams:(NSDictionary *)params inDB:(FMDatabase *)database{
    NSMutableString *sql = [[NSMutableString alloc] init];
    NSArray *allKeys = [params allKeys];
    [sql appendFormat:@"INSERT INTO %@(%@) VALUES (:%@)",tableName, [allKeys componentsJoinedByString:@","],[allKeys componentsJoinedByString:@",:"]];
    return [database executeUpdate:sql withParameterDictionary:params];
}

- (BOOL)updateTable:(NSString *)tableName withParams:(NSDictionary *)params where:(NSDictionary *)where inDB:(FMDatabase *)database{
    NSMutableString *sql = [[NSMutableString alloc] init];
    NSMutableArray *arrayValue = [NSMutableArray array];
    NSArray *allKeys = [params allKeys];
    for (NSString *key in allKeys) {
        [arrayValue addObject:[NSString stringWithFormat:@"%@ = ?",key]];
    }
    NSMutableArray *arrayWhere = [NSMutableArray array];
    allKeys = [where allKeys];
    for (NSString *key in allKeys) {
        [arrayWhere addObject:[NSString stringWithFormat:@"%@ = ?",key]];
    }
    [sql appendFormat:@"UPDATE %@ SET %@ WHERE %@",tableName,[arrayValue componentsJoinedByString:@","],[arrayWhere componentsJoinedByString:@" AND "]];
    NSMutableArray *values = [NSMutableArray array];
    [values addObjectsFromArray:[params allValues]];
    [values addObjectsFromArray:[where allValues]];
    return [database executeUpdate:sql withArgumentsInArray:values];
}

- (BOOL)deleteTable:(NSString *)tableName where:(NSDictionary *)where database:(FMDatabase *)database{
    NSArray *allKeys = [where allKeys];
    NSMutableArray *whereArray = [NSMutableArray array];
    for (NSString *key in allKeys) {
        [whereArray addObject:[NSString stringWithFormat:@"%@ = ?",key]];
    }
    NSMutableString *sql = [[NSMutableString alloc] init];
    [sql appendFormat:@"DELETE FROM %@ WHERE %@",tableName, [whereArray componentsJoinedByString:@" AND "]];
    return [database executeUpdate:sql withArgumentsInArray:[where allValues]];
}
#pragma mark - Public methods

+ (NSString *)databasePath{
    NSString *libraryPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
    return [NSString stringWithFormat:@"%@/%@.sqlite",libraryPath,kDatabaseName];
}

+ (void)copyDatabase{
    NSString *libraryPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
    NSString *dbPath = [NSString stringWithFormat:@"%@/%@.sqlite",libraryPath,kDatabaseName];
    if (![[NSFileManager defaultManager] fileExistsAtPath:dbPath]) {
        [[NSFileManager defaultManager] copyItemAtPath:[[NSBundle mainBundle] pathForResource:kDatabaseName ofType:@"sqlite"] toPath:dbPath error:nil];
    }
}


#pragma mark - Methods query


- (NSMutableArray *)getFoodsListInDate:(NSString *)date{
    NSMutableArray *arr = [NSMutableArray array];
    if(![self.db open]){
        NSLog(@"getFoodsList: can't open the database");
        return arr;
    }
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM eating_table WHERE eating_date = '%@' ORDER BY eating_time",date];
    FMResultSet *result = [self.db executeQuery:sql];
    while ([result next]) {
        CALEating *e = [[CALEating alloc] init];
        e.eatingId = [result intForColumn:@"eating_id"];
        e.eatingName = [result stringForColumn:@"eating_name"];
        e.eatingTime = [result stringForColumn:@"eating_time"];
        e.eatingDate = [result stringForColumn:@"eating_date"];
        NSString *sql1 = [NSString stringWithFormat:@"SELECT SUM(track_cals) FROM eating_foods_table WHERE eating_id = %d", e.eatingId];
        FMResultSet *r = [self.db executeQuery:sql1];
        if ([r next]) {
            e.totalCals = [r doubleForColumnIndex:0];
        }
        [r close];
        
        [arr addObject:e];
    }
    [result close];
    [self.db close];
    return arr;
}

//get information diary===========================
- (NSMutableArray *)getDiaryInfomation{
    NSMutableArray *arr = [NSMutableArray array];
    if (![self.db open]) {
        NSLog(@"cannot open database");
        return arr;
    }
    NSString *sql = @"SELECT * FROM diary_table";
    FMResultSet *result = [self.db executeQuery:sql];
    while([result next]){
        CALDiaryInfo *diaryInf = [[CALDiaryInfo alloc] init];
        diaryInf.diaryId = [result intForColumn:@"diary_id"];
        diaryInf.diaryWaist = [result doubleForColumn:@"diary_waist"];
        diaryInf.diaryWeight = [result doubleForColumn:@"diary_weight"];
        diaryInf.diaryHip = [result doubleForColumn:@"diary_hip"];
        diaryInf.diaryBreast = [result doubleForColumn:@"diary_breast"];
        diaryInf.diaryDate = [result stringForColumn:@"diary_date"];
        [arr addObject:diaryInf];
    }
    [result close];
    [self.db close];
    return  arr;
}

- (CALDiaryInfo *)getDiaryInfoByDate:(NSDate *)date{
    if (![self.db open]) {
        NSLog(@"cannot open database");
        return nil;
    }
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM diary_table WHERE diary_date = '%@'",[date dateString]];
    FMResultSet *result = [self.db executeQuery:sql];
    CALDiaryInfo *diaryInf = nil;
    if([result next]){
        diaryInf = [[CALDiaryInfo alloc] init];
        diaryInf.diaryId = [result intForColumn:@"diary_id"];
        diaryInf.diaryWaist = [result doubleForColumn:@"diary_waist"];
        diaryInf.diaryWeight = [result doubleForColumn:@"diary_weight"];
        diaryInf.diaryHip = [result doubleForColumn:@"diary_hip"];
        diaryInf.diaryBreast = [result doubleForColumn:@"diary_breast"];
        diaryInf.diaryDate = [result stringForColumn:@"diary_date"];
    }
    NSLog(@"info ==>> %f", diaryInf.diaryWeight);
    [result close];
    [self.db close];
    return diaryInf;
}

//============================================-add weight
- (BOOL)addWeight:(float) weight inTime:(NSDate *)time{
    NSDictionary *dic = [NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithFloat:weight], @"diary_weight", [time dateString],@"diary_date", nil];
    BOOL result;
    CALDiaryInfo *diaryInfo = [self getDiaryInfoByDate:time];
    if (![self.db open]) {
        NSLog(@"add Weight in table: cant open database");
        return NO;
    }
    if (diaryInfo.diaryWeight) {
        NSDictionary *dicWhere = [NSDictionary dictionaryWithObjectsAndKeys:[time dateString],@"diary_date", nil];
        result = [self updateTable:@"diary_table" withParams:dic where:dicWhere inDB:self.db];
    }
    else{
           result = [self insertTable:@"diary_table" withParams:dic inDB:self.db];
    }
    [self.db close];
    return result;
}

//=================================================- add Measurement
- (BOOL)addMeasurement:(float) hipSize WaistInfo:(float) waistSize NeckCircumInfo:(float) neckCircum inTime:(NSDate *)time{
    if (![self.db open]) {
        NSLog(@"add Measurement in table: cant open database");
        return NO;
    }
    NSDictionary *dic = [NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithFloat:hipSize],@"diary_hip",[NSNumber numberWithFloat:waistSize],@"diary_waist",[NSNumber numberWithFloat:neckCircum],@"diary_breast",[time dateString], @"diary_date", nil];
    BOOL result = [self insertTable:@"diary_table" withParams:dic inDB:self.db];
    [self.db close];
    return  result;
}

//==================================================
- (BOOL)addEatingWithName:(NSString *)name time:(NSString *)time date:(NSString *)date{
    if (![self.db open]) {
        NSLog(@"addEatingWithName: can't open the database");
        return NO;
    }
    BOOL result = [self insertTable:@"eating_table" withParams:[NSDictionary dictionaryWithObjectsAndKeys:name,@"eating_name",time,@"eating_time",date,@"eating_date", nil] inDB:self.db];
    [self.db close];
    return result;
}



- (NSMutableArray *)getFoodsWithEatingId:(NSInteger)eatingId{
    NSMutableArray *result = [NSMutableArray array];
    if(![self.db open]){
        NSLog(@"getFoodsWithEatingId: can't open the database");
        return result;
    }
    
    NSString *sql = [NSString stringWithFormat:@"SELECT f.food_id,f.food_name,f.food_image, ef.track_cals FROM eating_foods_table ef INNER JOIN foods_table f ON ef.food_id = f.food_id WHERE ef.eating_id = %d",eatingId];
    
    FMResultSet *rs = [self.db executeQuery:sql];
    while ([rs next]) {
        CALFood *f = [[CALFood alloc] init];
        f.foodId = [rs intForColumn:@"food_id"];
        f.foodName = [rs stringForColumn:@"food_name"];
        f.foodImage = [rs stringForColumn:@"food_image"];
        f.cals = [rs doubleForColumn:@"track_cals"];
        [result addObject:f];
    }
    [rs close];
    [self.db close];
    return result;
}


- (NSMutableArray *)getAllFoods{
    NSMutableArray *results = [NSMutableArray array];
    if(![self.db open]){
        NSLog(@"getAllFoods: can't open the database");
        return results;
    }
    
    NSString *sql = @"SELECT * FROM foods_table ORDER BY food_name";
    FMResultSet *rs = [self.db executeQuery:sql];
    while ([rs next]) {
        CALFood *f = [[CALFood alloc] init];
        f.foodId = [rs intForColumn:@"food_id"];
        f.foodName = [rs stringForColumn:@"food_name"];
        f.foodImage = [rs stringForColumn:@"food_image"];
        f.cals = [rs doubleForColumn:@"food_cals"];
        [results addObject:f];
    }
    [rs close];
    [self.db close];
    return results;
}

- (BOOL)updateFood:(CALFood *)food eatingId:(NSInteger)eatingId{
    if(![self.db open]){
        NSLog(@"updateFoods: can't open the database");
        return NO;
    }
    NSMutableDictionary *params = [NSMutableDictionary dictionary];
    [params setObject:[NSNumber numberWithInteger:food.foodId] forKey:@"food_id"];
    [params setObject:[NSNumber numberWithInteger:eatingId] forKey:@"eating_id"];
    [params setObject:[NSNumber numberWithDouble:food.cals] forKey:@"track_cals"];
    BOOL result = [self insertTable:@"eating_foods_table" withParams:params inDB:self.db];
    [self.db close];
    return result;
}

- (double)getCalsInDate:(NSString *)date{
    double cals = 0;
    if(![self.db open]){
        NSLog(@"getCalsInDate: can't open the database");
        return cals;
    }
    
    NSString *sql = [NSString stringWithFormat:@"SELECT SUM(ef.track_cals) FROM eating_foods_table ef INNER JOIN eating_table e ON ef.eating_id = e.eating_id WHERE e.eating_date = '%@'",date];
    
    FMResultSet *rs = [self.db executeQuery:sql];
    if([rs next]){
        cals = [rs doubleForColumnIndex:0];
    }
    [rs close];
    [self.db close];
    
    return cals;
}

- (BOOL)updateHipSize:(double)hs waistSize:(double)ws neckSize:(double)ns inDate:(NSString *)date{
    if(![self.db open]){
        NSLog(@"updateHipSize: can't open the database");
        return NO;
    }
    BOOL value = NO;
    NSString *sql = [NSString stringWithFormat:@"SELECT diary_id FROM diary_table WHERE diary_date = '%@'",date];
    FMResultSet *rs = [self.db executeQuery:sql];
    if([rs next]){
        value = [self updateTable:@"diary_table" withParams:[NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithDouble:hs],@"diary_hip",[NSNumber numberWithDouble:ws],@"diary_waist",[NSNumber numberWithDouble:ns],@"diary_breast", nil] where:[NSDictionary dictionaryWithObjectsAndKeys:date,@"diary_date", nil] inDB:self.db];
    }else{
        value = [self insertTable:@"diary_table" withParams:[NSDictionary dictionaryWithObjectsAndKeys:date,@"diary_date",[NSNumber numberWithDouble:hs],@"diary_hip",[NSNumber numberWithDouble:ws],@"diary_waist",[NSNumber numberWithDouble:ns],@"diary_breast", nil] inDB:self.db];
    }
    [rs close];
    [self.db close];
    return value;
}

- (BOOL)deleteFood:(CALFood *)food{
    if(![self.db open]){
        return NO;
    }
    
    BOOL result = [self deleteTable:@"foods_table" where:[NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithInteger:food.foodId],@"food_id", nil] database:self.db];
    
    result = result && [self deleteTable:@"eating_foods_table" where:[NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithInteger:food.foodId],@"food_id", nil] database:self.db];
    
    [self.db close];
    
    return result;
}
- (BOOL)deleteFood:(CALFood *)food eatingId:(NSInteger)eatingId{
    if(![self.db open]){
        return NO;
    }
    
    BOOL result = [self deleteTable:@"eating_foods_table" where:[NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithInteger:food.foodId],@"food_id",[NSNumber numberWithInteger:eatingId],@"eating_id", nil] database:self.db];
    [self.db close];
    return result;
}
- (BOOL)deleteEatingWithId:(NSInteger)eatingId{
    if(![self.db open]){
        return NO;
    }
    
    BOOL result = [self deleteTable:@"eating_table" where:[NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithInteger:eatingId],@"eating_id", nil] database:self.db];
    
    result = result && [self deleteTable:@"eating_foods_table" where:[NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithInteger:eatingId], @"eating_id", nil] database:self.db];
    
    [self.db close];
    return result;
}

//==========================----add new food
- (CALFood *)getFoodWithName:(NSString *)foodName{
    if (![self.db open]) {
        NSLog(@"cannot open database");
        return nil;
    }
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM foods_table WHERE food_name = '%@'",foodName];
    FMResultSet *result = [self.db executeQuery:sql];
    CALFood *foodInf = nil;
    if([result next]){
        foodInf = [[CALFood alloc] init];
        foodInf.foodName = [result stringForColumn:@"food_name"];
        foodInf.foodId = [result intForColumn:@"food_id"];
        foodInf.foodImage = [result stringForColumn:@"food_image"];
        foodInf.cals = [result doubleForColumn:@"food_cals"];
    }
    [result close];
    [self.db close];
    return foodInf;
}

- (BOOL)addNewFood:(CALFood *)newFood{
    NSDictionary *dic = [NSDictionary dictionaryWithObjectsAndKeys:newFood.foodName,@"food_name",[NSNumber numberWithDouble:newFood.cals], @"food_cals", nil];
    if (![self.db open]) {
        return NO;
    }
    BOOL result = [self insertTable:@"foods_table" withParams:dic inDB:self.db];
    [self.db close];
    return result;
}

- (BOOL)updateExistEating:(CALEating *)eating{
    if(![self.db open]){
        return NO;
    }
    
    BOOL result = [self updateTable:@"eating_table" withParams:[NSDictionary dictionaryWithObjectsAndKeys:eating.eatingName,@"eating_name",eating.eatingTime,@"eating_time", nil] where:[NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithInteger:eating.eatingId],@"eating_id", nil] inDB:self.db];
    
    [self.db close];
    return result;
}


- (double)getFoodCalUnit:(NSInteger)foodId{
    if(![self.db open]){
        return 0;
    }
    
    NSString *sql = [NSString stringWithFormat:@"SELECT food_cals FROM foods_table WHERE food_id = %d",foodId];
    double result = 0;
    FMResultSet *rs = [self.db executeQuery:sql];
    if ([rs next]) {
        result  = [rs doubleForColumnIndex:0];
    }
    [rs close];
    [self.db close];
    return result;
}



- (BOOL)updateFoodAmount:(CALFood *)food eatingId:(NSInteger)eatingId{
    if(![self.db open]){
        return NO;
    }
    
    BOOL result = [self updateTable:@"eating_foods_table" withParams:[NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithDouble:food.cals],@"track_cals", nil] where:[NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithInteger:food.foodId],@"food_id",[NSNumber numberWithInteger:eatingId],@"eating_id", nil] inDB:self.db];
    
    [self.db close];
    return result;
}


//ok
//- (NSArray *)designers{
//    NSMutableArray *arr = [NSMutableArray array];
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return arr;
//    }
//    NSString *sql = @"SELECT BrandUnid, BrandDesc FROM Designers WHERE IsActive = 1 ORDER BY BrandDesc";
//    FMResultSet *result = [self.db executeQuery:sql];
//    
//    while([result next]){
//        Designer *d = [[Designer alloc] init];
//        d.brandDesc = [result stringForColumn:@"BrandDesc"];
//        d.brandId   = [result stringForColumn:@"BrandUnid"];
//        [arr addObject:d];
//        [d release];
//    }
//    [result close];
//    [result close];
//    return arr;
//}
//
////ok
//- (NSArray *)productsOfDesigner:(NSString *)designerId{
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return nil;
//    }
//    NSString *sql = [NSString stringWithFormat:@"SELECT ProductUnid, \"Product Name\", ImageFileName FROM Products WHERE BrandUnid = '%@' AND ProductTypeUnid = '%@'",designerId,kWeddingDressId];
//    NSLog(@"sql: %@",sql);
//    FMResultSet *result = [self.db executeQuery:sql];
//    NSMutableArray *arr = [NSMutableArray array];
//    while ([result next]) {
//        Product *p = [[Product alloc] init];
//        p.productId = [result stringForColumn:@"ProductUnid"];
//        p.productName = [result stringForColumn:@"Product Name"];
//        p.productImage = [[result stringForColumn:@"ImageFileName"] stringByReplacingOccurrencesOfString:@"\\" withString:@"/"];
//        [arr addObject:p];
//        [p release];
//    }
//    [result close];
//    [self.db close];
//    return arr;
//}
//
////ok
//- (BOOL)user:(NSInteger)userId likeProduct:(NSString *)productId brand:(NSString *)brandId{
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return NO;
//    }
//    BOOL result = [self insertTable:@"Favourites" withParams:[NSDictionary dictionaryWithObjectsAndKeys:productId, @"productId", [NSNumber numberWithInteger:userId], @"userId", brandId, @"brandId", nil] inDB:self.db];
//    [self.db close];
//    return result;
//}
//
////ok
//- (BOOL)user:(NSInteger)userId unlikeProduct:(NSString *)productId{
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return NO;
//    }
//    BOOL result = [self deleteTable:@"Favourites" where:[NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithInteger:userId], @"userId", productId, @"productId", nil] database:self.db];
//    [self.db close];
//    
//    return result;
//}
//
////ok
//- (NSInteger)favouriteCountOfUser:(NSInteger)userId{
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return -1;
//    }
//    NSString *sql = [NSString stringWithFormat:@"SELECT count(*) FROM Favourites WHERE userId = %d",userId];
//    FMResultSet *result = [self.db executeQuery:sql];
//    NSInteger value = 0;
//    if ([result next]) {
//        value = [result intForColumnIndex:0];
//    }
//    [result close];
//    [self.db close];
//    return value;
//}
//
////ok
//- (NSInteger)acceptUsername:(NSString *)username email:(NSString *)email weddingDate:(NSString *)date{
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return -1;
//    }
//    
//    NSString *sql = [NSString stringWithFormat:@"SELECT UserId FROM Users WHERE email = '%@'",email];
//    FMResultSet *result = [self.db executeQuery:sql];
//    NSInteger userId = -1;
//    if ([result next]) {
//        userId = [result intForColumn:@"UserId"];
//    }
//    [result close];
//    
//    if (userId == -1) {
//        [self insertTable:@"Users" withParams:[NSDictionary dictionaryWithObjectsAndKeys:username, @"Username", email, @"email", date, @"WeddingDate", nil] inDB:self.db];
//        
//        sql = [NSString stringWithFormat:@"SELECT UserId FROM Users WHERE email = '%@' ",email];
//        result = [self.db executeQuery:sql];
//        if ([result next]) {
//            userId = [result intForColumn:@"UserId"];
//        }
//        [result close];
//    }else{
//        [self updateTable:@"Users" withParams:[NSDictionary dictionaryWithObjectsAndKeys:username, @"Username", email, @"email", date, @"WeddingDate", nil] where:[NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithInteger:userId], @"UserId", nil] inDB:self.db];
//    }
//    [self.db close];
//    return userId;
//}
//
////ok
//- (NSArray *)favouriteIdsOfUser:(NSInteger)userId{
//    NSMutableArray *values = [NSMutableArray array];
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return values;
//    }
//    NSString *sql = [NSString stringWithFormat:@"SELECT productId FROM Favourites WHERE userId = %d",userId];
//    FMResultSet *result = [self.db executeQuery:sql];
//    while([result next]){
//        [values addObject:[result stringForColumn:@"productId"]];
//    }
//    [result close];
//    [self.db close];
//    return values;
//}
//
//
////ok
//- (NSArray *)favouritesOfUser:(NSInteger)userId{
//     NSMutableArray *values = [NSMutableArray array];
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return values;
//    }
//    NSString *sql = [NSString stringWithFormat:@"SELECT p.ProductUnid, p.\"Product Name\", p.ImageFileName, p.Brand, p.BrandUnid FROM Products p INNER JOIN Favourites f ON p.ProductUnid = f.productId WHERE f.userId = %d",userId];
//    FMResultSet *result = [self.db executeQuery:sql];
//    while([result next]){
//        Product *p = [[Product alloc] init];
//        p.productId = [result stringForColumn:@"ProductUnid"];
//        p.productName = [result stringForColumn:@"Product Name"];
//        p.productImage = [[result stringForColumn:@"ImageFileName"] stringByReplacingOccurrencesOfString:@"\\" withString:@"/"];
//        p.isFavourite = YES;
//        Designer *d = [[Designer alloc] init];
//        d.brandId = [result stringForColumn:@"BrandUnid"];
//        d.brandDesc = [result stringForColumn:@"Brand"];
//        p.designer = d;
//        [d release];
//        [values addObject:p];
//        [p release];
//    }
//    [result close];
//    [self.db close];
//    return values;
//}

//ok
//- (Product *)productDetailsById:(NSString *)productId{
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return nil;
//    }
//    
//    NSString *sql = [NSString stringWithFormat:@"SELECT \"Style Code\", Collection, BrandUnid, Brand, \"Available Colours\", \"Available in Sizes\" FROM Products WHERE ProductUnid = '%@'",productId];
//    
//    FMResultSet *result = [self.db executeQuery:sql];
//    Product *p = [[[Product alloc] init] autorelease];
//    if([result next]){
////        p.productId = [result stringForColumn:@"ProductUnid"];
////        p.productImage = [[result stringForColumn:@"ImageFileName"] stringByReplacingOccurrencesOfString:@"\\" withString:@"/"];
////        p.productName = [result stringForColumn:@"Product Name"];
//        p.styleCode = [result stringForColumn:@"Style Code"];
//        p.collection = [result stringForColumn:@"Collection"];
//        Designer *d = [[Designer alloc] init];
//        d.brandId = [result stringForColumn:@"BrandUnid"];
//        d.brandDesc = [result stringForColumn:@"Brand"];
//        p.designer = d;
//        [d release];
//        p.colours = [result stringForColumn:@"Available Colours"];
//        p.sizes = [result stringForColumn:@"Available in Sizes"];
//    }else
//        p = nil;
//    [result close];
//    
//    if(p != nil){
//        sql = [NSString stringWithFormat:@"SELECT Attribute, Value FROM ProductAttributes WHERE ProductUnid = '%@'",productId];
//        result = [self.db executeQuery:sql];
//        NSMutableDictionary *attributes = [NSMutableDictionary dictionary];
//        while ([result next]) {
//            [attributes setObject:[result stringForColumn:@"Value"] forKey:[result stringForColumn:@"Attribute"]];
//        }
//        [result close];
//        p.attributes = attributes;
//    }
//    
//    [self.db close];
//    return p;
//}


//#define DEG2RAD(degrees) (degrees * 0.01745327) // degrees * pi over 180

//static void distanceFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
//{
//    // check that we have four arguments (lat1, lon1, lat2, lon2)
//    assert(argc == 4);
//    // check that all four arguments are non-null
//    if (sqlite3_value_type(argv[0]) == SQLITE_NULL || sqlite3_value_type(argv[1]) == SQLITE_NULL || sqlite3_value_type(argv[2]) == SQLITE_NULL || sqlite3_value_type(argv[3]) == SQLITE_NULL) {
//        sqlite3_result_null(context);
//        return;
//    }
//    // get the four argument values
//    double lat1 = sqlite3_value_double(argv[0]);
//    double lon1 = sqlite3_value_double(argv[1]);
//    double lat2 = sqlite3_value_double(argv[2]);
//    double lon2 = sqlite3_value_double(argv[3]);
//    // convert lat1 and lat2 into radians now, to avoid doing it twice below
//    double lat1rad = DEG2RAD(lat1);
//    double lat2rad = DEG2RAD(lat2);
//    // apply the spherical law of cosines to our latitudes and longitudes, and set the result appropriately
//    // 6378.1 is the approximate radius of the earth in kilometres
//    sqlite3_result_double(context, acos(sin(lat1rad) * sin(lat2rad) + cos(lat1rad) * cos(lat2rad) * cos(DEG2RAD(lon2) - DEG2RAD(lon1))) * 6378.1);
//}

//- (NSArray *)stockistsNearLocation:(CLLocationCoordinate2D)location{
//    NSMutableArray *list = [NSMutableArray array];
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return list;
//    }
//    
//    [self.db makeFunctionNamed:@"distance" maximumArguments:4 withBlock:^(sqlite3_context *context, int argc, sqlite3_value **argv) {
//        // check that we have four arguments (lat1, lon1, lat2, lon2)
//        assert(argc == 4);
//        // check that all four arguments are non-null
//        if (sqlite3_value_type(argv[0]) == SQLITE_NULL || sqlite3_value_type(argv[1]) == SQLITE_NULL || sqlite3_value_type(argv[2]) == SQLITE_NULL || sqlite3_value_type(argv[3]) == SQLITE_NULL) {
//            sqlite3_result_null(context);
//            return;
//        }
//        // get the four argument values
//        double lat1 = sqlite3_value_double(argv[0]);
//        double lon1 = sqlite3_value_double(argv[1]);
//        double lat2 = sqlite3_value_double(argv[2]);
//        double lon2 = sqlite3_value_double(argv[3]);
//        // convert lat1 and lat2 into radians now, to avoid doing it twice below
//        double lat1rad = DEG2RAD(lat1);
//        double lat2rad = DEG2RAD(lat2);
//        // apply the spherical law of cosines to our latitudes and longitudes, and set the result appropriately
//        // 6378.1 is the approximate radius of the earth in kilometres
//        sqlite3_result_double(context, acos(sin(lat1rad) * sin(lat2rad) + cos(lat1rad) * cos(lat2rad) * cos(DEG2RAD(lon2) - DEG2RAD(lon1))) * 6378.1);
//
//    }];
//    
//    NSString *sql = [NSString stringWithFormat:@"SELECT s.RetailerUnid, s.RetailerName, s.BrandUnid, r.Address1, r.Address2,r.Address3, r.Phone, r.Email, r.Website, r.Longitude, r.Latitude FROM Retailers r INNER JOIN Stockists s ON r.Unid = s.RetailerUnid WHERE distance(r.Latitude,r.Longitude,%f,%f) < 100 AND r.IsActive = 1 GROUP BY s.RetailerUnid ORDER BY s.RetailerName",location.latitude,location.longitude];
////    NSString *sql = [NSString stringWithFormat:@"SELECT s.RetailerUnid, s.RetailerName, s.BrandUnid, r.Address1, r.Address2,r.Address3, r.Phone, r.Email, r.Website, r.Longitude, r.Latitude FROM Retailers r INNER JOIN Stockists s ON r.Unid = s.RetailerUnid WHERE distance(r.Latitude,r.Longitude,%f,%f) < 20 AND r.IsActive = 1 GROUP BY s.RetailerUnid ORDER BY s.RetailerName",53.810016,-3.046392];
//    NSLog(@"sql : %@",sql);
//    FMResultSet *result = [self.db executeQuery:sql];
//    
//    while ([result next]) {
//        Stockist *s = [[Stockist alloc] init];
//        s.retailerId = [result stringForColumn:@"RetailerUnid"];
//        s.brandId = [result stringForColumn:@"BrandUnid"];
//        s.retailerName = [result stringForColumn:@"RetailerName"];
//        NSMutableArray *address = [NSMutableArray array];
//        NSString *ad1 = [result stringForColumn:@"Address1"];
//        if(ad1 != nil)
//            [address addObject:ad1];
//        NSString *ad2 = [result stringForColumn:@"Address2"];
//        if(ad2 != nil)
//            [address addObject:ad2];
//        NSString *ad3 = [result stringForColumn:@"Address3"];
//        if(ad3 != nil)
//            [address addObject:ad3];
//        s.address = address;
//        NSString *phone = [result stringForColumn:@"Phone"];
//        if(phone != nil)
//            s.phoneNumber = phone;
//        NSString *email = [result stringForColumn:@"Email"];
//        if (email != nil) {
//            s.email = email;
//        }
//        NSString *website = [result stringForColumn:@"Website"];
//        if (website != nil) {
//            s.website = website;
//        }
//        
//        CLLocationCoordinate2D location;
//        location.latitude = [result doubleForColumn:@"Latitude"];
//        location.longitude = [result doubleForColumn:@"Longitude"];
//        s.location = location;
//        
//        //        NSString *sql1 = [NSString stringWithFormat:@"SELECT count(*) FROM Favourites INNER JOIN Stockists ON Favourites.brandId = Stockists.BrandUnid WHERE Favourites.userId = %d AND Stockists.RetailerUnid = '%@'",userId, s.retailerId];
//        //        FMResultSet *result2 = [self.db executeQuery:sql1];
//        //        if ([result2 next]) {
//        //            s.favouriteCount = [result2 intForColumnIndex:0];
//        //        }
//        //        [result2 close];
//        
//        //        sql = [NSString stringWithFormat:@"SELECT ImageFileName FROM Products INNER JOIN Favourites ON Products.ProductUnid = Favourites.productId WHERE Favourites.userId = %d AND Products.BrandUnid IN (SELECT Stockists.BrandUnid FROM Stockists WHERE Stockists.RetailerUnid = '%@')",userId,s.retailerId];
//        //        FMResultSet *result2 = [self.db executeQuery:sql];
//        //        NSMutableArray *array = [NSMutableArray array];
//        //        while ([result2 next]) {
//        //            [array addObject:[result2 stringForColumn:@"ImageFileName"]];
//        //        }
//        //        [result2 close];
//        ////        NSLog(@"image: %@",[array componentsJoinedByString:@","]);
//        //        s.favouriteProducts = array;
//        [list addObject:s];
//        [s release];
//    }
//    [result close];
//    
//    
//    [self.db close];
//    return list;
//}
//
//
////ok
//- (NSArray *)stockistsWithBrandId:(NSString *)brandId ofUser:(NSInteger)userId{
//    NSMutableArray *list = [NSMutableArray array];
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return list;
//    }
//    
//    NSString *sql = [NSString stringWithFormat:@"SELECT s.RetailerUnid, s.RetailerName, s.BrandUnid, r.Address1, r.Address2,r.Address3, r.Phone, r.Email, r.Website, r.Longitude, r.Latitude FROM Retailers r INNER JOIN Stockists s ON r.Unid = s.RetailerUnid WHERE s.BrandUnid = '%@' AND r.IsActive = 1 GROUP BY s.RetailerUnid ORDER BY s.RetailerName",brandId];
//    
//    FMResultSet *result = [self.db executeQuery:sql];
//    
//    while ([result next]) {
//        Stockist *s = [[Stockist alloc] init];
//        s.retailerId = [result stringForColumn:@"RetailerUnid"];
//        s.brandId = [result stringForColumn:@"BrandUnid"];
//        s.retailerName = [result stringForColumn:@"RetailerName"];
//        NSMutableArray *address = [NSMutableArray array];
//        NSString *ad1 = [result stringForColumn:@"Address1"];
//        if(ad1 != nil)
//            [address addObject:ad1];
//        NSString *ad2 = [result stringForColumn:@"Address2"];
//        if(ad2 != nil)
//            [address addObject:ad2];
//        NSString *ad3 = [result stringForColumn:@"Address3"];
//        if(ad3 != nil)
//            [address addObject:ad3];
//        s.address = address;
//        NSString *phone = [result stringForColumn:@"Phone"];
//        if(phone != nil)
//            s.phoneNumber = phone;
//        NSString *email = [result stringForColumn:@"Email"];
//        if (email != nil) {
//            s.email = email;
//        }
//        NSString *website = [result stringForColumn:@"Website"];
//        if (website != nil) {
//            s.website = website;
//        }
//        
//        CLLocationCoordinate2D location;
//        location.latitude = [result doubleForColumn:@"Latitude"];
//        location.longitude = [result doubleForColumn:@"Longitude"];
//        s.location = location;
//        
////        NSString *sql1 = [NSString stringWithFormat:@"SELECT count(*) FROM Favourites INNER JOIN Stockists ON Favourites.brandId = Stockists.BrandUnid WHERE Favourites.userId = %d AND Stockists.RetailerUnid = '%@'",userId, s.retailerId];
////        FMResultSet *result2 = [self.db executeQuery:sql1];
////        if ([result2 next]) {
////            s.favouriteCount = [result2 intForColumnIndex:0];
////        }
////        [result2 close];
//        
////        sql = [NSString stringWithFormat:@"SELECT ImageFileName FROM Products INNER JOIN Favourites ON Products.ProductUnid = Favourites.productId WHERE Favourites.userId = %d AND Products.BrandUnid IN (SELECT Stockists.BrandUnid FROM Stockists WHERE Stockists.RetailerUnid = '%@')",userId,s.retailerId];
////        FMResultSet *result2 = [self.db executeQuery:sql];
////        NSMutableArray *array = [NSMutableArray array];
////        while ([result2 next]) {
////            [array addObject:[result2 stringForColumn:@"ImageFileName"]];
////        }
////        [result2 close];
//////        NSLog(@"image: %@",[array componentsJoinedByString:@","]);
////        s.favouriteProducts = array;
//        [list addObject:s];
//        [s release];
//    }
//    [result close];
//    [self.db close];
//    return list;
//}
//
////ok
//- (NSArray *)getFavouriteProductImagesWithRetailerId:(NSString *)retailerId userId:(NSInteger)userId{
//    NSMutableArray *list = [NSMutableArray array];
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return list;
//    }
//    NSString *sql = [NSString stringWithFormat:@"SELECT ImageFileName FROM Products INNER JOIN Favourites ON Products.ProductUnid = Favourites.productId WHERE Favourites.userId = %d AND Products.BrandUnid IN (SELECT Stockists.BrandUnid FROM Stockists WHERE Stockists.RetailerUnid = '%@')",userId,retailerId];
//    FMResultSet *result = [self.db executeQuery:sql];
//    while ([result next]) {
//        [list addObject:[[result stringForColumn:@"ImageFileName"] stringByReplacingOccurrencesOfString:@"\\" withString:@"/"]];
//    }
//    [result close];
//    [self.db close];
//    return list;
//}
//
////ok
//- (NSArray *)getAttributes{
//    NSMutableArray *list = [NSMutableArray array];
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return list;
//    }
//    
//    NSString *sql = [NSString stringWithFormat:@"SELECT Unid, Caption From Attributes WHERE ProductTypeUnid = '%@'",kWeddingDressId];
//    FMResultSet *result = [self.db executeQuery:sql];
//    while([result next]){
//        Attribute *a = [[Attribute alloc] init];
//        a.attributeId = [result stringForColumn:@"Unid"];
//        a.attributeName = [result stringForColumn:@"Caption"];
//        [list addObject:a];
//        [a release];
//    }
//    [result close];
//    [self.db close];
//    return list;
//}
//
////ok
//- (NSArray *)getAttributeValuesById:(NSString *)attributeId{
//    NSMutableArray *list = [NSMutableArray array];
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return list;
//    }
//    
//    NSString *sql = [NSString stringWithFormat:@"SELECT v.Unid, v.Caption From AttributeValues v  WHERE v.AttributeUnid = '%@' ORDER BY v.Caption",attributeId];
//    FMResultSet *result = [self.db executeQuery:sql];
//    while([result next]){
//        AttributeValue *a = [[AttributeValue alloc] init];
//        a.attributeValueId = [result stringForColumn:@"Unid"];
//        a.attributeId = attributeId;
//        a.attributeValue = [result stringForColumn:@"Caption"];
//        [list addObject:a];
//        [a release];
//    }
//    [result close];
//    [self.db close];
//    return list;
//}
//
////products: array productId
////designers: array designerId
////colours: array colours
////themes: array theme
////attributeValues: array attributeValueIds
//- (NSArray *)filterProducts:(NSArray *)products withDesigners:(NSArray *)designers colours:(NSArray *)colours themes:(NSArray *)theme attributeValues:(NSArray *)attributeValues{
//    NSMutableArray *list = [NSMutableArray arrayWithArray:products];
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return list;
//    }
//    if(designers != nil || colours != nil || theme != nil){
//        NSMutableString *sql = [NSMutableString string];
//        [sql appendFormat:@"SELECT ProductUnid FROM Products WHERE ProductUnid IN ('%@') ",[products componentsJoinedByString:@"','"]];
//        
//        if(designers != nil){
//            [sql appendFormat:@"AND BrandUnid IN ('%@') ",[designers componentsJoinedByString:@"','"]];
//        }
//        
//        if (colours != nil) {
//            [sql appendString:@"AND (\"Available Colours\" LIKE '%"];
//            [sql appendString:[colours componentsJoinedByString:@"%' OR \"Available Colours\" LIKE '%"]];
//            [sql appendString:@"%') "];
//        }
//        
//        NSLog(@"sql filter: %@", sql);
//        
//        //implement filter by theme?
//        
//        NSMutableArray *filter1 = [NSMutableArray array];
//        
//        FMResultSet *result = [self.db executeQuery:sql];
//        while ([result next]) {
//            [filter1 addObject:[result stringForColumn:@"ProductUnid"]];
//        }
//        [result close];
//        list = filter1;
//    }
//    
//    if(attributeValues != nil){
//        NSMutableString *sql = [NSMutableString string];
//        [sql appendFormat:@"SELECT pa.ProductUnid FROM ProductAttributes pa WHERE pa.ProductUnid IN ('%@') AND pa.AttributeValueUnid IN ('%@') GROUP BY pa.ProductUnid",[list componentsJoinedByString:@"','"],[attributeValues componentsJoinedByString:@"','"]];
//        FMResultSet *result2 = [self.db executeQuery:sql];
//        NSMutableArray *filter2 = [NSMutableArray array];
//        while ([result2 next]) {
//            [filter2 addObject:[result2 stringForColumn:@"ProductUnid"]];
//        }
//        [result2 close];
//        list = filter2;
//    }
//    
//    [self.db close];
//    return list;
//}
//
//- (NSArray *)searchProductsBySilhouette:(NSString *)silhouettle neckline:(NSString *)neckline range:(RRP)range{
//    NSMutableArray *list = [NSMutableArray array];
//    if (![self.db open]) {
//        NSLog(@"can't open database");
//        return list;
//    }
//    
//    NSMutableString *sql = [NSMutableString stringWithFormat:@"SELECT p.ProductUnid, p.\"Product Name\", p.Brand, p.BrandUnid, p.ImageFileName FROM Products p INNER JOIN ProductAttributes pa ON p.ProductUnid = pa.ProductUnid WHERE p.ProductTypeUnid = '%@' ",kWeddingDressId];
//    if(silhouettle != nil){
//        [sql appendFormat:@"AND pa.AttributeValueUnid = '%@' ",silhouettle];
//    }
//    
//    if(neckline != nil){
//        [sql appendFormat:@"AND pa.AttributeValueUnid = '%@'",neckline];
//    }
//    
//    if (range.start >= 0) {
//        if(range.start == 2000){
//            [sql appendFormat:@"AND p.RRP > %d ",range.start];
//        }else{
//            [sql appendFormat:@"AND (p.RRP BETWEEN %d AND %d) ",range.start + 1, range.start + range.length];
//        }
//    }
//    [sql appendString:@"GROUP BY p.ProductUnid"];
//    
//    NSLog(@"sql query: %@",sql);
//    
//    FMResultSet *result = [self.db executeQuery:sql];
//    while ([result next]) {
//        Product *p = [[Product alloc] init];
//        p.productId = [result stringForColumn:@"ProductUnid"];
//        p.productName = [result stringForColumn:@"Product Name"];
//        p.productImage = [[result stringForColumn:@"ImageFileName"] stringByReplacingOccurrencesOfString:@"\\" withString:@"/"];
//        Designer *d = [[Designer alloc] init];
//        d.brandId = [result stringForColumn:@"BrandUnid"];
//        d.brandDesc = [result stringForColumn:@"Brand"];
//        p.designer = d;
//        [d release];
//        [list addObject:p];
//        [p release];
//    }
//    [result close];
//    [self.db close];
//    return list;
//}

@end
